<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 6.3.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon.jpg">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon.jpg">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"matian.connorma.cn","root":"/","scheme":"Muse","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}}};
  </script>

  <meta name="description" content="mysql在group by之后如何取出每个group下的topN？在mysql 8之前，使用max&#x2F;min函数能够取出group后某列的top1的值，但是面对topN的问题无能为力，通过子查询的方式进行解决；mysql 8引入的窗口函数对于此类组内统计很实用">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql如何取出每个group的topN">
<meta property="og:url" content="http://matian.connorma.cn/5b501ae4.html">
<meta property="og:site_name" content="马天">
<meta property="og:description" content="mysql在group by之后如何取出每个group下的topN？在mysql 8之前，使用max&#x2F;min函数能够取出group后某列的top1的值，但是面对topN的问题无能为力，通过子查询的方式进行解决；mysql 8引入的窗口函数对于此类组内统计很实用">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2021-09-22T07:38:43.000Z">
<meta property="article:modified_time" content="2024-06-02T11:03:44.677Z">
<meta property="article:author" content="马天">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">

<link rel="canonical" href="http://matian.connorma.cn/5b501ae4.html">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>mysql如何取出每个group的topN | 马天</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">马天</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://matian.connorma.cn/5b501ae4.html">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="马天">
      <meta itemprop="description" content="程序猿">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="马天">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          mysql如何取出每个group的topN
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2021-09-22 15:38:43" itemprop="dateCreated datePublished" datetime="2021-09-22T15:38:43+08:00">2021-09-22</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/mysql/" itemprop="url" rel="index"><span itemprop="name">mysql</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <p>mysql在group by之后如何取出每个group下的topN？在mysql 8之前，使用max&#x2F;min函数能够取出group后某列的top1的值，但是面对topN的问题无能为力，通过子查询的方式进行解决；mysql 8引入的窗口函数对于此类组内统计很实用</p>
<span id="more"></span>

<p>取topN的情况并不少见，以下我们给出一个示例——实际上这个问题也是在编写查询zabbix监控数据的sql时遇到的，示例使用一个简化的表说明核心问题</p>
<p>一个用于存储监控数据的表t_monitor_history：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `t_monitor_history` (</span><br><span class="line">  `clock` <span class="type">bigint</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;时间戳(s)&#x27;</span>,</span><br><span class="line">  `monitor_key` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;监控项key&#x27;</span>,</span><br><span class="line">  `<span class="keyword">value</span>` <span class="type">bigint</span> <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;监控值&#x27;</span>,</span><br><span class="line">  KEY `clock` (`clock`),</span><br><span class="line">  KEY `monitor_key` (`monitor_key`)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8mb3;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126861</span>, <span class="string">&#x27;memory.used&#x27;</span>, <span class="number">1024</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126861</span>, <span class="string">&#x27;memory.total&#x27;</span>, <span class="number">2048</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126862</span>, <span class="string">&#x27;fs.used(/)&#x27;</span>, <span class="number">10240</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126860</span>, <span class="string">&#x27;fs.total(/)&#x27;</span>, <span class="number">20480</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126700</span>, <span class="string">&#x27;memory.used&#x27;</span>, <span class="number">128</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126701</span>, <span class="string">&#x27;memory.total&#x27;</span>, <span class="number">2048</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126704</span>, <span class="string">&#x27;fs.used(/)&#x27;</span>, <span class="number">5120</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126703</span>, <span class="string">&#x27;fs.total(/)&#x27;</span>, <span class="number">40960</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126499</span>, <span class="string">&#x27;memory.used&#x27;</span>, <span class="number">199</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126501</span>, <span class="string">&#x27;memory.total&#x27;</span>, <span class="number">1000</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126501</span>, <span class="string">&#x27;fs.used(/)&#x27;</span>, <span class="number">512</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126502</span>, <span class="string">&#x27;fs.total(/)&#x27;</span>, <span class="number">900</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>第一个需求：查询每个监控项的最近一次采集值。直接对monitor_key列group后，对clock取max，即可找出每组的monitor_key和对应的最近一次采集值的clock，通过这两个列的值筛选即可：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	DATE_FORMAT( FROM_UNIXTIME( t.clock ), <span class="string">&#x27;%Y-%m-%d %H:%i:%s&#x27;</span> ) <span class="keyword">AS</span> dt,</span><br><span class="line">	t.monitor_key,</span><br><span class="line">	t.`<span class="keyword">value</span>` </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	t_monitor_history t</span><br><span class="line">	<span class="keyword">JOIN</span> ( </span><br><span class="line">	  <span class="keyword">SELECT</span> monitor_key, <span class="built_in">max</span>( clock ) <span class="keyword">AS</span> max_clock </span><br><span class="line">	    <span class="keyword">FROM</span> t_monitor_history </span><br><span class="line">	  <span class="keyword">GROUP</span> <span class="keyword">BY</span> monitor_key ) tmp </span><br><span class="line">	<span class="keyword">ON</span> t.clock <span class="operator">=</span> tmp.max_clock </span><br><span class="line">	<span class="keyword">AND</span> t.monitor_key <span class="operator">=</span> tmp.monitor_key </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span></span><br><span class="line">	clock <span class="keyword">DESC</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ------ RESULT ---------------------------------</span></span><br><span class="line"><span class="comment">-- dt                   monitor_key       value</span></span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.used	      1024</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:02	fs.used(/)	      10240</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:00	fs.total(/)	      20480</span></span><br></pre></td></tr></table></figure>

<p>然后进一步，让我们进入正题：如何查询每个监控项的最近2条采集值记录（注意，我们希望只取2条，即使clock出现并列的情况）？</p>
<h2 id="union-all合并多个查询的结果集"><a href="#union-all合并多个查询的结果集" class="headerlink" title="union all合并多个查询的结果集"></a>union all合并多个查询的结果集</h2><p>分别查询每个group的top2（where group &#x3D; xx order by … limit 2），使用union all合并结果集——这一方法在小型数据集的分析上是比较简洁的，但需要预先确定数据特征，并不是一个通用解</p>
<h2 id="定义session内变量循环查找"><a href="#定义session内变量循环查找" class="headerlink" title="定义session内变量循环查找"></a>定义session内变量循环查找</h2><p>如何解决？在StackOverflow上等网站上有热心网友提供了通过在sql语句内定义session级变量进行循环查找的方式，但本人实测性能下降显著。</p>
<p>这类回答不少，贴个链接作为<a target="_blank" rel="noopener" href="https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results">参考</a></p>
<h2 id="where条件使用子查询筛选group内topN"><a href="#where条件使用子查询筛选group内topN" class="headerlink" title="where条件使用子查询筛选group内topN"></a>where条件使用子查询筛选group内topN</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	DATE_FORMAT( FROM_UNIXTIME( his.clock ), <span class="string">&#x27;%Y-%m-%d %H:%i:%s&#x27;</span> ) <span class="keyword">AS</span> dt,</span><br><span class="line">	monitor_key,</span><br><span class="line">	`<span class="keyword">value</span>` </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	t_monitor_history <span class="keyword">AS</span> his </span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	( <span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="keyword">FROM</span> t_monitor_history <span class="keyword">AS</span> tmp <span class="keyword">WHERE</span> his.monitor_key <span class="operator">=</span> tmp.monitor_key <span class="keyword">AND</span> tmp.clock <span class="operator">&gt;=</span> his.clock ) <span class="operator">&lt;=</span> <span class="number">2</span> </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span></span><br><span class="line">	his.monitor_key <span class="keyword">DESC</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ------ RESULT ---------------------------------</span></span><br><span class="line"><span class="comment">-- dt                   monitor_key       value</span></span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.used	      1024</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:20	memory.used	      128</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:21	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:02	fs.used(/)	      10240</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:24	fs.used(/)	      5120</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:00	fs.total(/)	      20480</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:23	fs.total(/)	      40960</span></span><br></pre></td></tr></table></figure>

<p>这一方法简洁易实现，但是测试中发现在特定情况下存在问题：如果某个group内的排序列clock的第2名并列，那么只会返回第1位的结果，如下，为memory.used这一个监控项添加一个clock并列第2位的记录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `sql_test`.`t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126700</span>, <span class="string">&#x27;memory.used&#x27;</span>, <span class="number">-1</span>);</span><br></pre></td></tr></table></figure>
<p>执行结果成为了：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- ------ RESULT ---------------------------------</span></span><br><span class="line"><span class="comment">-- dt                   monitor_key       value</span></span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.used	      1024</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:21	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:02	fs.used(/)	      10240</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:24	fs.used(/)	      5120</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:00	fs.total(/)	      20480</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:23	fs.total(/)	      40960</span></span><br></pre></td></tr></table></figure>

<h2 id="使用group-concat和find-in-set取group内某列topN"><a href="#使用group-concat和find-in-set取group内某列topN" class="headerlink" title="使用group_concat和find_in_set取group内某列topN"></a>使用group_concat和find_in_set取group内某列topN</h2><p>group_concat用于列转行，将多行的值合并到一行内，默认使用’,’连接为一个字符串；find_in_set可在’,’连接的多个字符串中查找出指定的字符串，返回值为0(查找的字符串不存在)，或查找的字符串在字符串列表中的索引值（索引值从1开始）</p>
<p>sql整体采取与取max同样的思路：对monitor_key列group后，找出top2的clock值，通过这两个列的值进行筛选。问题就来到了如何找出每个group内top2的clock值，结合使用group_concat和find_in_set即可实现</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	DATE_FORMAT( FROM_UNIXTIME( t.clock ), <span class="string">&#x27;%Y-%m-%d %H:%i:%s&#x27;</span> ) <span class="keyword">AS</span> dt,</span><br><span class="line">	t.monitor_key,</span><br><span class="line">	t.`<span class="keyword">value</span>` </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	t_monitor_history t</span><br><span class="line">	<span class="keyword">JOIN</span> ( </span><br><span class="line">		<span class="keyword">SELECT</span> monitor_key, SUBSTRING_INDEX( GROUP_CONCAT( clock <span class="keyword">ORDER</span> <span class="keyword">BY</span> clock <span class="keyword">DESC</span> ), <span class="string">&#x27;,&#x27;</span>, <span class="number">2</span> ) <span class="keyword">AS</span> max_two_clock_list </span><br><span class="line">		  <span class="keyword">FROM</span> t_monitor_history </span><br><span class="line">		<span class="keyword">GROUP</span> <span class="keyword">BY</span> monitor_key ) tmp </span><br><span class="line">	<span class="keyword">ON</span> t.monitor_key <span class="operator">=</span> tmp.monitor_key </span><br><span class="line">	<span class="keyword">AND</span> FIND_IN_SET( t.clock, tmp.max_two_clock_list ) <span class="operator">&gt;</span> <span class="number">0</span> </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span></span><br><span class="line">	monitor_key <span class="keyword">DESC</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ------ RESULT ---------------------------------</span></span><br><span class="line"><span class="comment">-- dt                   monitor_key       value</span></span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.used	      1024</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:20	memory.used	      128</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:21	memory.total      2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:02	fs.used(/)	      10240</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:24	fs.used(/)	      5120</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:00	fs.total(/)	      20480</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:23	fs.total(/)	      40960</span></span><br></pre></td></tr></table></figure>

<p>这里在子查询中，首先通过GROUP_CONCAT函数以降序拼接clock值到一行中，然后使用SUBSTRING_INDEX按照分隔符’,’进行截取得到子字符串，分割2次即保留了每个group内最大的两个clock值；之后在外部的筛选中，通过FIND_IN_SET函数过滤出所有子查询结果集提供的clock值，即得到了每个group中clock最大的top2记录</p>
<p>然而与上一条类似，因为外层查询使用top2的clock进行筛选，这一方法在clock列出现并列时会将查询所有并列的记录。插入一条并列记录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `sql_test`.`t_monitor_history` (`clock`, `monitor_key`, `<span class="keyword">value</span>`) <span class="keyword">VALUES</span> (<span class="number">631126700</span>, <span class="string">&#x27;memory.used&#x27;</span>, <span class="number">-1</span>);</span><br></pre></td></tr></table></figure>

<p>执行结果变成了：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- ------ RESULT ---------------------------------</span></span><br><span class="line"><span class="comment">-- dt                   monitor_key       value</span></span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.used	      1024</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:20	memory.used	      128</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:20	memory.used	      -1</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:21	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:02	fs.used(/)	      10240</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:24	fs.used(/)	      5120</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:00	fs.total(/)	      20480</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:23	fs.total(/)	      40960</span></span><br></pre></td></tr></table></figure>

<p>在实际的查询中，通过在子查询和外部查询添加where条件过滤clock和monitor_key值，可以更充分的利用索引，达到更高的效率</p>
<h2 id="mysql-8使用窗口函数"><a href="#mysql-8使用窗口函数" class="headerlink" title="mysql 8使用窗口函数"></a>mysql 8使用窗口函数</h2><p>mysql 8引入了窗口函数，关于窗口函数的使用示例可以参考网友的<a target="_blank" rel="noopener" href="https://zhuanlan.zhihu.com/p/132477535">这一篇介绍文章</a></p>
<p>使用窗口函数对clock列按照monitor_key分组后排序，并使用ROW_NUMBER()排序生成排序次序列r，在外层查询中使用r筛选即可轻松取到每个分组内按照clock取topN的行：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	dt,</span><br><span class="line">	monitor_key,</span><br><span class="line">	`<span class="keyword">value</span>` </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	(</span><br><span class="line">	<span class="keyword">SELECT</span></span><br><span class="line">		DATE_FORMAT( FROM_UNIXTIME( clock ), <span class="string">&#x27;%Y-%m-%d %H:%i:%s&#x27;</span> ) <span class="keyword">AS</span> dt,</span><br><span class="line">		monitor_key,</span><br><span class="line">		`<span class="keyword">value</span>`,</span><br><span class="line">		<span class="built_in">ROW_NUMBER</span>() <span class="keyword">over</span> ( <span class="keyword">PARTITION</span> <span class="keyword">BY</span> monitor_key <span class="keyword">ORDER</span> <span class="keyword">BY</span> clock <span class="keyword">DESC</span> ) <span class="keyword">AS</span> r </span><br><span class="line">	<span class="keyword">FROM</span></span><br><span class="line">		t_monitor_history </span><br><span class="line">	) t </span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	r <span class="operator">&lt;=</span> <span class="number">2</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ------ RESULT ---------------------------------</span></span><br><span class="line"><span class="comment">-- dt                   monitor_key       value</span></span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:00	fs.total(/)	      20480</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:23	fs.total(/)	      40960</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:02	fs.used(/)	      10240</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:24	fs.used(/)	      5120</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:21	memory.total	  2048</span></span><br><span class="line"><span class="comment">-- 1990-01-01 01:01:01	memory.used	      1024</span></span><br><span class="line"><span class="comment">-- 1990-01-01 00:58:20	memory.used	      128</span></span><br></pre></td></tr></table></figure>

<p>ROW_NUMBER()不会考虑并列值，而是为每行生成一个序号，因此group内clock值出现重复时也能准确的取出topN——如果希望取出并列值，可以使用RANK函数</p>

    </div>

    
    
    

      <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/mysql/" rel="tag"># mysql</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/81ee92e1.html" rel="prev" title="mysql8 with使用与场景示例：用户邀请记录查询">
      <i class="fa fa-chevron-left"></i> mysql8 with使用与场景示例：用户邀请记录查询
    </a></div>
      <div class="post-nav-item">
    <a href="/cfe640d.html" rel="next" title="mysql全文索引的使用">
      mysql全文索引的使用 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#union-all%E5%90%88%E5%B9%B6%E5%A4%9A%E4%B8%AA%E6%9F%A5%E8%AF%A2%E7%9A%84%E7%BB%93%E6%9E%9C%E9%9B%86"><span class="nav-number">1.</span> <span class="nav-text">union all合并多个查询的结果集</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%AE%9A%E4%B9%89session%E5%86%85%E5%8F%98%E9%87%8F%E5%BE%AA%E7%8E%AF%E6%9F%A5%E6%89%BE"><span class="nav-number">2.</span> <span class="nav-text">定义session内变量循环查找</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#where%E6%9D%A1%E4%BB%B6%E4%BD%BF%E7%94%A8%E5%AD%90%E6%9F%A5%E8%AF%A2%E7%AD%9B%E9%80%89group%E5%86%85topN"><span class="nav-number">3.</span> <span class="nav-text">where条件使用子查询筛选group内topN</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8group-concat%E5%92%8Cfind-in-set%E5%8F%96group%E5%86%85%E6%9F%90%E5%88%97topN"><span class="nav-number">4.</span> <span class="nav-text">使用group_concat和find_in_set取group内某列topN</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#mysql-8%E4%BD%BF%E7%94%A8%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0"><span class="nav-number">5.</span> <span class="nav-text">mysql 8使用窗口函数</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">马天</p>
  <div class="site-description" itemprop="description">程序猿</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">41</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">17</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">25</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="https://github.com/matian2014" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;matian2014" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="mailto:matian1103@qq.com" title="E-Mail → mailto:matian1103@qq.com" rel="noopener" target="_blank"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
      </span>
  </div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        
  <div class="beian"><a href="https://beian.miit.gov.cn/" rel="noopener" target="_blank">陕ICP备2023001563号 </a>
  </div>

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2025</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">马天</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://muse.theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/muse.js"></script>


<script src="/js/next-boot.js"></script>




  















  

  

</body>
</html>
